package com.googlecode.garbagecan.dbtest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;


public class ChargeBack {
	private Random random = new Random(0);
	public static void main(String[]args) throws Exception {
		ChargeBack cb = new ChargeBack();
		if(args.length == 0) {
			cb.insert();
			cb.update();
			System.out.println("DO NOTHING!");
			return;
		} else if (args[0].equals("insert")){
			System.out.println("Insert ...");
			cb.insert();
		} else if (args[0].equals("update")){
			System.out.println("Update ...");
			cb.update();
		} else {
			System.out.println("DO NOTHING!");
		}
	}
	
	private void insert() throws Exception {
		String sql = this.getInsertSql();
		Connection conn = this.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.YEAR, 2009);
		cal.set(Calendar.MONTH, 0); // 0 - 11
		cal.set(Calendar.DAY_OF_MONTH, 1); // 1 - ..
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		
		int timeStep = 60;
		int countStep = 0;
		
		int own1 = 20;
		int own2 = 20;
		
		float total1 = 60 * 24 * 31 * own1;
		float total2 = 60 * 24 * 31 * own2;
		
		float total_used1 = 0;
		float total_used2 = 0;
		
		int borrow1 = 0;
		int lend1 = 0;
		
		int borrow2 = 0;
		int lend2 = 0;
		
		while(true) {
			total_used1 += (own1 + borrow1 - lend1) * timeStep;
			total_used2 += (own2 + borrow2 - lend2) * timeStep;
			
			ps.setObject(1, new Timestamp(cal.getTimeInMillis()));
			ps.setObject(2, cal.getTimeInMillis());
			ps.setObject(3, "clusterZ");
			ps.setObject(4, "/citi_group/Fox/Analytics/SW/Production");
			ps.setObject(5, "SW");
			ps.setObject(6, "Analytics");
			ps.setObject(7, "Production");
			ps.setObject(8, own1);
			ps.setObject(9, borrow1);
			ps.setObject(10, lend1);
			ps.setObject(11, own1 * timeStep);
			ps.setObject(12, borrow1 * timeStep);
			ps.setObject(13, lend1 * timeStep);
			float totle_plan1 = total1 / (60 * 24 * 31) * timeStep * countStep;
			ps.setObject(14, totle_plan1);
			ps.setObject(15, total_used1);
			ps.setObject(16, total_used1);
			
			ps.execute();
			
			ps.setObject(1, new Timestamp(cal.getTimeInMillis()));
			ps.setObject(2, cal.getTimeInMillis());
			ps.setObject(3, "clusterZ");
			ps.setObject(4, "/citi_group/Fox/Analytics_2/SW/Production");
			ps.setObject(5, "SW");
			ps.setObject(6, "Analytics_2");
			ps.setObject(7, "Production");
			ps.setObject(8, own2);
			ps.setObject(9, borrow2);
			ps.setObject(10, lend2);
			ps.setObject(11, own2 * timeStep);
			ps.setObject(12, borrow2 * timeStep);
			ps.setObject(13, lend2 * timeStep);
			float totle_plan2 = total2 / (60 * 24 * 31) * timeStep * countStep;
			ps.setObject(14, totle_plan2);
			ps.setObject(15, total_used2);
			ps.setObject(16, total_used2);
			
			ps.execute();
			
			conn.commit();
			
			cal.add(Calendar.MINUTE, timeStep);
			countStep ++;
			borrow1 = getRandom();
			lend2 = borrow1;
			if(cal.get(Calendar.MONTH) == 1) {
				break;
			}
		}
		ps.close();
		conn.close();
	}
	
	private void update() throws Exception {
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		cal.set(Calendar.MILLISECOND, 0);
		
		Connection conn = this.getConnection();
		
		String s1 = "update charge_back set TOTAL_USED=TOTAL_FUTURE where TOTAL_FUTURE is not null";
		String s2 = "update charge_back set TOTAL_FUTURE=null where time_stamp<?";
		String s3 = "update charge_back set TOTAL_USED=null where time_stamp>=?";
		
		PreparedStatement ps1 = conn.prepareStatement(s1);
		ps1.execute();
		conn.commit();
		
		PreparedStatement ps2 = conn.prepareStatement(s2);
		ps2.setObject(1, new Timestamp(cal.getTimeInMillis()));
		ps2.execute();
		conn.commit();
		
		PreparedStatement ps3 = conn.prepareStatement(s3);
		ps3.setObject(1, new Timestamp(cal.getTimeInMillis()));
		ps3.execute();
		conn.commit();
	}
	
	private String getInsertSql() throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append("insert into CHARGE_BACK(");
		sb.append("TIME_STAMP").append(",");
		sb.append("TIME_STAMP_GMT").append(",");
		sb.append("CLUSTER_NAME").append(",");
		sb.append("CONSUMER").append(",");
		sb.append("DATACENTER_NAME").append(",");
		sb.append("APPLICATION_NAME").append(",");
		sb.append("APPLICATION_GROUP").append(",");
		sb.append("OWN").append(",");
		sb.append("BORROW").append(",");
		sb.append("LEND").append(",");
		sb.append("OWN_MINUTE").append(",");
		sb.append("BORROW_MINUTE").append(",");
		sb.append("LEND_MINUTE").append(",");
		sb.append("TOTAL_PLAN").append(",");
		sb.append("TOTAL_USED").append(",");
		sb.append("TOTAL_FUTURE").append(")");
		sb.append("values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		return sb.toString();
	}

	private Connection getConnection() throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//String url = "jdbc:oracle:thin:@//ib04b01:1521/oracle1";
		//Connection conn = DriverManager.getConnection(url, "as7", "as7");
		String url = "jdbc:oracle:thin:@//172.17.18.189:1521/orcl";
		Connection conn = DriverManager.getConnection(url, "fkong", "fkong");
		return conn;
	}

	private Date parseToDate(String s) throws ParseException {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = df.parse(s);
		return date;
	}
	
	private int getRandom() {
		int t = 0;
		while(true) {
			t = random.nextInt();
			if(t > 0) {
				break;
			}
		}
		
		return t % 5;
	}
}
/*
CREATE TABLE CHARGE_BACK
(
   TIME_STAMP timestamp NOT NULL,
   TIME_STAMP_GMT int NOT NULL,
   CLUSTER_NAME varchar(128) NOT NULL,
   CONSUMER varchar(128) NOT NULL,
   DATACENTER_NAME varchar(128) NOT NULL,
   APPLICATION_NAME varchar(256) NOT NULL,
   APPLICATION_GROUP varchar(128) NOT NULL,
   OWN int NOT NULL,
   BORROW int NOT NULL,
   LEND int NOT NULL,
   OWN_MINUTE int NOT NULL,
   BORROW_MINUTE int NOT NULL,
   LEND_MINUTE int NOT NULL,
   TOTAL_PLAN float ,
   TOTAL_USED float ,
   TOTAL_FUTURE float 
);
*/